/*
 * 李宇版权所有 
 */
package com.zbdx.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zbdx.bean.Course;
import com.zbdx.bean.Teacher;
import com.zbdx.bean.teacherDto;
import com.zbdx.dao.teacherDao;

import com.zbdx.util.DBUtil;

/**
 *	teacherDao实现类
 * @author liyu
 * @version 1.0
 */
public class teacherDaoImpl implements teacherDao {

	@Override
	public int updateTeacher(Teacher teacher) {
		Connection conn  = DBUtil.getConnection();
		PreparedStatement  pstmt = null;
		int count = 0;
		String sql = "UPDATE teacher SET tel=?,TeacherTitle=?,EntryDate=?,Birther=? ,Stuatus=? WHERE TeacherName=?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, teacher.getTel());
			pstmt.setString(2, teacher.getTeacherTitle());
			pstmt.setString(3, teacher.getEntryDate());
			pstmt.setString(4, teacher.getBirther());
			pstmt.setString(5, teacher.getStuatus());
			pstmt.setString(6, teacher.getTeacherName());
			count = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResource(null, pstmt, conn);
		}
		return count;
	}

	@Override
	public boolean login(String teacherName, String password) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		boolean loginFlag=false;
		conn = DBUtil.getConnection();
		String sql = "SELECT*FROM teacher WHERE TeacherName=? AND Password=?";
		
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, teacherName);
			pstmt.setString(2, password);
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				loginFlag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(rs, pstmt, conn);
		}
		return loginFlag;
	}

	@Override
	public boolean insertTeacher(Teacher teacher) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		boolean insertFlag=false;
		conn =DBUtil.getConnection();
		
		String sql = "INSERT INTO  teacher(collegeId,Schoolid,TeacherName,"
				+ "Password,IdCard,Sex,TeacherTitle,EntryDate,Birther,"
				+ "Stuatus,Tel,Email,Address) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		int count=0;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt .setInt(1, teacher.getCollegeid());
			pstmt .setInt(2, teacher.getSchooLid());
			pstmt .setString(3, teacher.getTeacherName());
			pstmt .setString(4, teacher.getPassword());
			pstmt .setString(5, teacher.getIdCard());
			pstmt .setString(6, teacher.getSex());
			pstmt .setString(7, teacher.getTeacherTitle());
			pstmt .setString(8, teacher.getEntryDate());
			pstmt .setString(9, teacher.getBirther());
			pstmt .setString(10, teacher.getStuatus());
			pstmt .setString(11, teacher.getTel());
			pstmt .setString(12, teacher.getEmail());
			pstmt .setString(13, teacher.getAddress());
			//这个方法执行的是insert,update,delete语句
			//如果执行成功返回非0Int,失败返回0
			count = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResource(rs, pstmt, conn);
		}
		if (count!=0) {
			//添加成功
			insertFlag = true;
		}
		
		
 		return insertFlag;
	}

	@Override
	public List<teacherDto> selectCourse() {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<teacherDto> tDtos = new ArrayList<teacherDto>();
		String sql ="SELECT * FROM teacherview";
		try {
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				String courseName = rs.getString("CourseName");
				String studentId = rs.getString("StudentName");
				float score = rs.getFloat("Score");
				teacherDto tDto = new teacherDto(studentId, courseName, score);
				tDtos.add(tDto);
				//System.out.println(tDto);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResource(rs, stmt, conn);
		}
		
		return tDtos;
	}

	@Override
	public int deleteCourse(String courseName) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstmt = null;
		int count=0;
		
		String sql = "DELETE FROM course WHERE CourseName =?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, courseName);
			count = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(null, pstmt, conn);
		}
		return count;
	}

	@Override
	public boolean addCoures(Course course) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstmt = null;
		String sql = "INSERT INTO course(TeacherID,CourseName,CouresRoom,MaxStudentNum,collageld) VALUES(?,?,?,?,?)";
		int count = 0;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, course.getTeacherId());
			pstmt.setString(2, course.getCourseName());
			pstmt.setString(3, course.getCourseRoom());
			pstmt.setInt(4, course.getMaxStudentNum());
			pstmt.setInt(5, course.getCollageId());
			count =pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(null, pstmt, conn);
		}
		if (count!=0) {
			return true;
		}
		return false;
	}

	@Override
	public List<Teacher> findAllteacher() {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Teacher> tList = new ArrayList<Teacher>();
		String sql="SELECT * FROM teacher";
		try {
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				String teacherName = rs.getString("TeacherName");
				String Password = rs.getString("Password");
				String IdCard = rs.getString("IdCard");
				String Sex = rs.getString("Sex");
				String TeacherTitle = rs.getString("TeacherTitle");
				String EntryDate = rs.getString("EntryDate");
				String Birther = rs.getString("Birther");
				String Stuatus= rs.getString("Stuatus");
				String tel = rs.getString("tel");
				String Email = rs.getString("Email");
				String Address = rs.getString("Address");
				Teacher teacher = new Teacher(teacherName, Password, IdCard, Sex, TeacherTitle, EntryDate, Birther, Stuatus, tel, Email, Address);
				tList.add(teacher);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(rs, stmt, conn);
		}
		return tList;
	}

	@Override
	public List<Course> findAllcourse() {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Course>  cList= new ArrayList<Course>();
		String sql = "select * FROM course";
		try {
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				int teacherid= rs.getInt("TeacherID");
				String coursename = rs.getString("CourseName");
				String courseroom = rs.getString("CouresRoom");
				int max= rs.getInt("MaxStudentNum");
				int collageid= rs.getInt("collageld");
				Course course =new Course(teacherid, coursename, courseroom, max, collageid);
				cList.add(course);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResource(rs, stmt, conn);
		}
	
		return cList;
	}

	@Override
	public Teacher findbyName(String teachername) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM teacher WHERE TeacherName =?";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, teachername);
			rs = stmt.executeQuery();
			while (rs.next()) {
				String teacherName = rs.getString("TeacherName");
				String Password = rs.getString("Password");
				String IdCard = rs.getString("IdCard");
				String Sex = rs.getString("Sex");
				String TeacherTitle = rs.getString("TeacherTitle");
				String EntryDate = rs.getString("EntryDate");
				String Birther = rs.getString("Birther");
				String Stuatus= rs.getString("Stuatus");
				String tel = rs.getString("tel");
				String Email = rs.getString("Email");
				String Address = rs.getString("Address");
				Teacher teacher = new Teacher(teacherName, Password, IdCard, Sex, TeacherTitle, EntryDate, Birther, Stuatus, tel, Email, Address);
				return teacher;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResource(rs, stmt, conn);
		}
		return null;
	}

	@Override
	public int deleTeachers(String teachername) {
		int count=0;
		// TODO Auto-generated method stub
		return count;
	}

	

}
